﻿using System;
using System.Data.OleDb;
using System.Data;
using WorkOrder.Business;

namespace WorkOrder
{
    public class ExcelHelper
    {
        private static string connString;
        private static string excelFile;
        private static OleDbConnection objConn;

        #region 构造方法
        /// <summary>
        /// 不带Excel文件名的构造方法，如果使用此构造方法，则调用该类的所有函数时，都需要带Excel文件名的参数。
        /// </summary>
        public ExcelHelper() { }

        /// <summary>
        /// 带文件名的构造方法，使用此方法时，其他函数不需要带文件名的参数。
        /// </summary>
        /// <param name="excelFileName">Excel文件名</param>
        public ExcelHelper(string excelFileName)
        {
            excelFile = excelFileName;
            connString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                             "Data Source=" + excelFileName + ";Extended Properties=Excel 8.0;";
            objConn = new OleDbConnection(connString);
        }
        #endregion

        #region 打开连接
        public void OpenConnection(string excelFileName)
        {
            excelFile = excelFileName;
            connString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                             "Data Source=" + excelFileName + ";Extended Properties=Excel 8.0;";
            objConn = new OleDbConnection(connString);
            if (objConn!=null && objConn.State != ConnectionState.Open)
                try { objConn.Open(); }
                catch { }
        }
        public void OpenConnection()
        {
            if (objConn != null && objConn.State != ConnectionState.Open)
                try { objConn.Open(); }
                catch { }
        }
        #endregion

        #region 关闭连接
        public void CloseConnection()
        {
            if (objConn!=null && objConn.State != ConnectionState.Closed)
                try { objConn.Close(); objConn.Dispose(); }
                catch { }
        }
        public static void CloseConnection(OleDbCommand ocmd)
        {
            if (ocmd.Connection != null)
            {
                try { ocmd.Connection.Close(); }
                catch { }
            }
            if (ocmd != null)
            {
                try { ocmd.Dispose(); }
                catch { }
            }
        }
        #endregion 

        #region 返回sheet列表
        /// <summary>
        /// 返回excel文件的sheet列表
        /// </summary>
        /// <param name="excelFile">excel文件名</param>
        /// <returns>sheet列表</returns>
        public  String[] GetExcelSheetNames(string excelFile)
        {
            System.Data.DataTable dt = null;
            try
            {
                OpenConnection(excelFile);
                dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                if (dt == null)
                {
                    return null;
                }
                String[] excelSheets = new String[dt.Rows.Count];
                int i = 0;
                foreach (DataRow row in dt.Rows)
                {
                    excelSheets[i] = row["TABLE_NAME"].ToString();
                    i++;
                }
                return excelSheets;
            }
            catch (Exception ex)
            {
                return null;
                throw ex;
            }
            finally
            {
                CloseConnection();
                if (dt != null)
                {
                    dt.Dispose();
                }
            }
        }
        public  String[] GetExcelSheetNames()
        {
            System.Data.DataTable dt = null;
            try
            {
                OpenConnection();
                dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                if (dt == null)
                {
                    return null;
                }
                String[] excelSheets = new String[dt.Rows.Count];
                int i = 0;
                foreach (DataRow row in dt.Rows)
                {
                    excelSheets[i] = row["TABLE_NAME"].ToString();
                    i++;
                }
                return excelSheets;
            }
            catch (Exception ex)
            {
                return null;
                throw ex;
            }
            finally
            {
                CloseConnection();
                if (dt != null)
                {
                    dt.Dispose();
                }
            }
        }
        #endregion

        #region 返回一个sheet的所有字段
        public String[] GetExcelSheetFileds(string excelFile, string sheetName)
        {
            DataTable sheetColumn=null;
            try
            {
                OpenConnection(excelFile);
                sheetColumn = objConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Columns, new object[] { null, null, sheetName, null });
                String[] sheetColumns = new String[sheetColumn.Rows.Count];
                if (sheetColumns == null) return null;
                //int i = 0;
                for (int i = 0; i < sheetColumn.Rows.Count; i++)
                {
                    sheetColumns[i] = sheetColumn.Rows[i]["COLUMN_NAME"].ToString();
                }
                //foreach (DataRow drowColumns in sheetColumn.Rows)
                //{
                //    sheetColumns[i] = drowColumns["COLUMN_NAME"].ToString();
                //    i++;
                //}
                return sheetColumns;
            }
            catch
            {
                return null;
                throw;
            }
            finally
            {
                CloseConnection();
                if (sheetColumn != null) sheetColumn.Dispose();
            }
        }
        public String[] GetExcelSheetFileds(string sheetName)
        {
            DataTable sheetColumn = null;
            try
            {
                OpenConnection();
                sheetColumn = objConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Columns, new object[] { null, null, sheetName, null });
                String[] sheetColumns = new String[sheetColumn.Rows.Count];
                if (sheetColumns == null) return null;
                int i = 0;
                foreach (DataRow drowColumns in sheetColumn.Rows)
                {
                    sheetColumns[i] = drowColumns["COLUMN_NAME"].ToString();
                    i++;
                }
                return sheetColumns;
            }
            catch (Exception ex)
            {
                return null;
                throw ex;
            }
            finally
            {
                CloseConnection();
                if (sheetColumn != null) sheetColumn.Dispose();
            }
        }
        #endregion

        #region 返回指定sheet的数据
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sheetName"></param>
        /// <param name="rowsCount">所有行，参数值是：0</param>
        /// <returns></returns>
        public DataSet GetExcelSheetDatas(string sheetName, int rowsCount)
        {
            OleDbDataAdapter odar;
            try
            {
                OpenConnection();
                if (rowsCount > 0)
                    odar = new OleDbDataAdapter("select top " + rowsCount + " * from [" + sheetName + "]", objConn);
                else
                    odar = new OleDbDataAdapter("select * from [" + sheetName + "]", objConn);
                DataSet ds = new DataSet();
                odar.Fill(ds, "excel");
                if (odar != null) odar.Dispose();
                return ds;
            }
            catch (Exception ex)
            {
                return null;
                throw ex;
            }
            finally
            {
                CloseConnection();
            }
        }
        public DataSet GetExcelSheetDatas(string excelfile, string sheetName, int rowsCount)
        {
            OleDbDataAdapter odar;
            try
            {
                OpenConnection(excelfile);
                if (rowsCount > 0)
                    odar = new OleDbDataAdapter("select top " + rowsCount + " * from [" + sheetName + "]", objConn);
                else
                    odar = new OleDbDataAdapter("select * from [" + sheetName + "]", objConn);
                DataSet ds = new DataSet();
                odar.Fill(ds, "excel");
                if (odar != null) odar.Dispose();
                return ds;
            }
            catch
            {
                return null;
                throw;
            }
            finally
            {
                CloseConnection();
            }
        }
        #endregion

        #region 导入数据
        public object ImportToDB(string[] sqlstr)
        {
            return new DbHelper().ExecuteTrans(sqlstr);
        }
        #endregion

    }
}
